<?php
session_start();
$year_selected = $_SESSION['year'];

require_once("../../../../wp-blog-header.php");
//global $wp_query;
//global $wp,$wpdb;
$current_user = wp_get_current_user();
$user_id = $current_user->ID;


$year = date('Y');
$month = date('m');
if ($year != $year_selected ) {
    $year = $year_selected;
    $month = 12;
}

$month = $month - 1;

$month_name= array(
    "Enero",
    "Febrero",
    "Marzo",
    "Abril",
    "Mayo",
    "Junio",
    "Julio",
    "Agosto",
    "Setiembre",
    "Octubre",
    "Noviembre",
    "Diciembre"
    );


$consumption_water_registry = $wpdb->get_results($wpdb->prepare("select * from bpa_registry_consumption where user_id = %d and type_id = 1 order by year asc, month asc", $user_id));
$consumption_electricity_registry = $wpdb->get_results($wpdb->prepare("select * from bpa_registry_consumption where user_id = %d and type_id = 2 order by year asc, month asc", $user_id));
$consumption_fuel_registry = $wpdb->get_results($wpdb->prepare(
        "select c.user_id, c.year, c.month, c.quantity, c.price, st.name 
        from bpa_registry_consumption c
        left join bpa_consumption_subtype st
        on c.subtype_id = st.id
        where c.user_id = %d and c.type_id = 3
        order by c.year asc, c.month asc, c.subtype_id asc", 
        $user_id));

$nombre_empresa = $wpdb->get_results($wpdb->prepare("select user_id, meta_key,meta_value FROM wp_usermeta where (meta_key = 'name_profile') order by user_id",""));
$ruc_empresa = $wpdb->get_results($wpdb->prepare("select user_id, meta_key,meta_value FROM wp_usermeta where (meta_key = 'ruc_profile') order by user_id",""));



/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Ecopyme")
							 ->setLastModifiedBy("Ecopyme")
							 ->setTitle("Office 2007 XLSX Data")
							 ->setSubject("Office 2007 XLSX Data")
							 ->setDescription("Office 2007 XLSX Data")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Ecopyme");


for ($i=0; $i < $month ; $i++) { 
    $rubro_empresa = $wpdb->get_results($wpdb->prepare("select a.user_id, b.name as sector, c.name as subsector , month 
                                                    FROM `bpa_registry_sector` a 
                                                    inner JOIN bpa_company_sector b
                                                    on a.sector_id = b.id 
                                                    inner join bpa_company_subsector c 
                                                    on a.subsector_id = c.id 
                                                    where a.year = ". $year." and a.month = (".$i." + 1)  
                                                    order by a.user_id",""));
    $workers = $wpdb->get_results($wpdb->prepare("select * from bpa_registry_workers where year = ". $year." and month = (".$i." + 1)   order by user_id",""));
    $products = $wpdb->get_results($wpdb->prepare("select * FROM bpa_registry_product where  year = ". $year." and  month = (".$i." + 1)   order by user_id",""));
    /*$consumos = $wpdb->get_results($wpdb->prepare("select * FROM `bpa_registry_consumption` where year = 2015 and month = 1 order by user_id",""));*/
    $consumos_agua = $wpdb->get_results($wpdb->prepare("select * FROM bpa_registry_consumption where type_id = 1 and year = ". $year." and month = (".$i." + 1)   order by user_id",""));
    $consumos_energia = $wpdb->get_results($wpdb->prepare("select * FROM bpa_registry_consumption where type_id = 2 and year = ". $year." and month = (".$i." + 1)  order by user_id",""));
    $consumos_gasolina = $wpdb->get_results($wpdb->prepare("select * FROM bpa_registry_consumption where type_id = 3 and subtype_id = 1   and  year = ". $year." and month = (".$i." + 1) order by user_id",""));
    $consumos_diesel = $wpdb->get_results($wpdb->prepare("select * FROM bpa_registry_consumption where type_id = 3 and subtype_id = 2 and year = ". $year." and month = (".$i." + 1) order by user_id",""));
    $consumos_glp = $wpdb->get_results($wpdb->prepare("select * FROM bpa_registry_consumption where type_id = 3 and subtype_id = 3 and year = ". $year." and month = (".$i." + 1) order by user_id",""));
    $consumos_gnv = $wpdb->get_results($wpdb->prepare("select * FROM bpa_registry_consumption where type_id = 3 and subtype_id = 4 and year = ". $year." and month = (".$i." + 1)  order by user_id",""));
    $objPHPExcel->createSheet($i);
    
    $objPHPExcel->setActiveSheetIndex()->mergeCells('G1:H1');
    $objPHPExcel->setActiveSheetIndex()->mergeCells('I1:J1');
    $objPHPExcel->setActiveSheetIndex()->mergeCells('K1:L1');
    $objPHPExcel->setActiveSheetIndex()->mergeCells('M1:N1');
    $objPHPExcel->setActiveSheetIndex()->mergeCells('O1:P1');
    $objPHPExcel->setActiveSheetIndex()->mergeCells('Q1:R1');

    $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('A1', 'Nombre')
                ->setCellValue('B1', 'RUC')
                ->setCellValue('C1', 'Rubro')
                ->setCellValue('D1', 'Subrubro')
                ->setCellValue('E1', 'Numero de Trabajadores')
                ->setCellValue('F1', 'Unidad producida')
                ->setCellValue('G1', 'Consumo de agua del mes')
                ->setCellValue('G2', 'en S/.')
                ->setCellValue('H2', 'en M3')
                ->setCellValue('I1', 'Consumo de electricidad del mes') 
                ->setCellValue('I2', 'en S/.')
                ->setCellValue('J2', 'en Kw/h')
                ->setCellValue('K1', 'Consumo de Gasolina del mes')
                ->setCellValue('K2', 'en S/.')
                ->setCellValue('L2', 'en Gl.')
                ->setCellValue('M1', 'Consumo de Diesel del mes')
                ->setCellValue('M2', 'en S/.')
                ->setCellValue('N2', 'en Gl.')
                ->setCellValue('O1', 'Consumo de GLP del mes')
                ->setCellValue('O2', 'en S/.')
                ->setCellValue('P2', 'en Lt.')
                ->setCellValue('Q1', 'Consumo de GNV del mes')
                ->setCellValue('Q2', 'en S/.')
                ->setCellValue('R2', 'en M3.');
    $row = 3;
    foreach ( $nombre_empresa as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('A'.$row, $item->meta_value);
        $row++;
    }
    $row = 3;
    foreach ( $ruc_empresa as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('B'.$row, $item->meta_value);
        $row++;
    } 
    $row = 3;
    foreach ( $rubro_empresa as $item ) {
           $objPHPExcel->setActiveSheetIndex($i)
            ->setCellValue('C'.$row, $item->sector);
        $row++;
    } 
    $row = 3;
    foreach ( $rubro_empresa as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('D'.$row, $item->subsector);
        $row++;
    } 
    $row = 3;
    foreach ( $workers as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('E'.$row, $item->number);
        $row++;
    }
    $row = 3;
    foreach ( $products as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('F'.$row, $item->product);
        $row++;
    } 
    $row = 3;
    foreach ( $consumos_agua as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('G'.$row, $item->price)
                ->setCellValue('H'.$row, $item->quantity);

    $row++;
    }
    $row = 3;
    foreach ( $consumos_energia as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('I'.$row, $item->price)
                ->setCellValue('J'.$row, $item->quantity);

    $row++;
    } 
    $row = 3;

    foreach ( $consumos_gasolina as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('K'.$row, $item->price)
                ->setCellValue('L'.$row, $item->quantity);
    $row++;
    }
    $row = 3;

    foreach ( $consumos_diesel as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('M'.$row, $item->price)
                ->setCellValue('N'.$row, $item->quantity);
    $row++;
    } 
    $row = 3;

    foreach ( $consumos_glp as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('O'.$row, $item->price)
                ->setCellValue('P'.$row, $item->quantity);
    $row++;
    } 
    $row = 3;

    foreach ( $consumos_gnv as $item ) {
        $objPHPExcel->setActiveSheetIndex($i)
                ->setCellValue('Q'.$row, $item->price)
                ->setCellValue('R'.$row, $item->quantity);
    $row++;
    }
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('A')->setWidth(70); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('B')->setWidth(14); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('C')->setWidth(60); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('D')->setWidth(80); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('E')->setWidth(23); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('F')->setWidth(40); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('G')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('H')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('I')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('J')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('K')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('L')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('M')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('N')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('O')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('P')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('Q')->setWidth(20); 
    $objPHPExcel->getActiveSheet($i)->getColumnDimension('R')->setWidth(20);
    $objPHPExcel->getActiveSheet($i)->getStyle('A1:R1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet($i)->getStyle('G2:R2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    

    $objPHPExcel->getActiveSheet($i)->setTitle($month_name[$i]);
}




// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="export.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
